﻿


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE TRIGGER [dbo].[Arhiva_After_Update] 
   ON [dbo].[Arhiva]
   AFTER INSERT,UPDATE
AS 
BEGIN
	SET NOCOUNT ON;

    DECLARE @table TABLE 
    ( 
		Id_Medicament int,
		Cantitate float,
		Checked bit DEFAULT(0)
    )
    
    INSERT INTO @table(Id_Medicament,Cantitate)
    SELECT inserted.Id_Medicament, inserted.Cantitate 
    FROM inserted 
    LEFT OUTER JOIN Stoc_Critic sc
    on inserted.Id_Medicament = sc.Id_Medicament
    WHERE (sc.CantitateMaximaPeZi is null) OR (inserted.Cantitate > sc.CantitateMaximaPeZi)
    
    DECLARE @Id_Medicament int = NULL
    DECLARE @stoc float
    DECLARE @cantitate float 
    
    SELECT TOP 1 @Id_Medicament = Id_Medicament,@cantitate = Cantitate FROM @table WHERE Checked = 0
    
    WHILE @Id_Medicament IS NOT NULL
    BEGIN
		UPDATE TOP (1) @table 
		SET Checked = 1
		WHERE Id_Medicament = @Id_Medicament AND Checked = 0
		
		EXEC CalculeazaStocCritic @Id_Medicament,4,@stoc OUTPUT;
		
		MERGE INTO Stoc_Critic stocCritic
		USING (SELECT @Id_Medicament as Id_Medicament,@stoc as Stoc,@cantitate as Cantitate) stocActualizat
		ON stocCritic.Id_Medicament = stocActualizat.Id_Medicament
		WHEN MATCHED THEN
			UPDATE  
			SET Cantitate = stocActualizat.Stoc,
			    CantitateMaximaPeZi = stocActualizat.Cantitate
		WHEN NOT MATCHED THEN
			INSERT (Id_Medicament,Cantitate,CantitateMaximaPeZi)
			VALUES (stocActualizat.Id_Medicament,stocActualizat.Stoc,stocActualizat.Cantitate);
		
		SET @Id_Medicament = NULL
		SELECT TOP 1 @Id_Medicament = Id_Medicament FROM @table WHERE Checked = 0
    END

END



